﻿<cfcomponent>
	<cfset this.DSN = "Pathfinder">
	<!--- todo:
T = needs testing
F = Fixed
D = Done
? = undecided on approach
X = decided against

				1) coins/gems/jewelry/art percentage and breakdown
D					a) add slider input to determine % age of max gpv to split into coins/gems/jewelry/art
D					b) lookup table of gems.jewelry/art items
				2) special material
					a) armor
					b) shields
					c) weapons
				3) handle double weapons
				4) add weighting to all magic items in magicitems table
T					a) needs a lookup table to include (TreasureCategoryID, WeightedChance, MagicItemID)
				5) add weighting to weapon types
T					a) simple - 45%
T					b) martial - 35%
T					c) exotic - 20%
				6) add options to
					a) select source(s)
?						-- implications:
							if a source is not included, should things like potions and wands made by classes in those sources not appear?
							(eg a magus spell is from the CRB, but is lower level for the magus in the APG; if the APG is disallowed, then a magus could not have created the item)
					b) select certain item types to include/exclude
						i) by category (goods, trade goods etc)
						ii) by specific type (livestock, etc)
					c) include/exclude cursed items
						i) add IsCursed column to MagicItems table
						ii) mark cursed items into the proper item type (eg the Planar Invasion Shield is showing up in Minor Wondrous Items treasures)
				7) add max qty fields to goods/trade goods
D					a) select a random qty up to max for results
					b) eliminate (or combine) a duplicated item when generated
				8) UI - add "remove item" link to each item
					a) recalc "TotalGPV"
				9) UI - add "remove treasure" (X) button to remove a full treasure listing from the page
			   10) UI - add 'print' and 'pdf' generation buttons/functions
			   11) bane weapons - determine bane type
			   12) energy resistance armor - determine energy type
			   13) do I need a % variance explicitly built in?
			   14) save treasures to db?
			   15) add UI to CRUD items
			   		a) not sure what DB tables to allow CRUD
			   16) validate data
			   		a) potion allowed on spells table
			   		b) wand allowed on spells table
			   		c) itemtypeid = 0 in magic items table
			   		d) minor/med/major wondrous items
			   		e) caster level and type for
			   			i) potions
			   			ii) wands
			   			iii) scrolls
			   		f) weapons
			   			i) category (simple|martial|exotic)
			   			ii) damage type(s)
			   			iii) special qualities
			   			iv) class (amunition, light, one handed, etc)
			   	17) add NPC wealth treasure type and value lookup
			   	18) wands - 30% chance of indication/clue to function-command word-activation method
			   		a) need lookup table of description/inscriptions/engravings
			   	19) armor/weapons - 10% are small
			   	20) %-age weapons shed light
			   		a) 30% of weapons glow
			   			i) some specific items always or never shed light--need a column in the db to indicate this
D			   	21) calculate percent of max qty of ammo
D			   		a) recalc prorated value
					b) combine & recalc if same item is generated again?
D			   	22) need to return the caster level for most items -- this will set the base CR to identify the items
F				23) something not working right for multiple spells on a single scroll
				24) remove special materials items (silver dagger, adamantine shield/armor etc) from magic items
					a) add a % chance that non-magic items are of a special material
				25) add costly spell components
					a) to spells db
D					b) to wand/scroll/potions price calculations
				26) duplicate spells in list
					a) Interplanetary Teleport
					b) Vermin Shape I
					c) Vermin Shape II
I					-- all spells are from both SourceID = 3 & 6 (Ultimate Magic & Inner Sea World Guide). Likely solution is remove ISWG version from list.
				27) ranged and melee weapons have different weighted values for special abilities!
D					a) need to add weapontypeid column to magicweaponspecialabilitiesweightedlookup table; current rows are for weapontypeid = 1 (melee)
D						i) add ranged weapons special abilities and weights to table
D					b) does this mean I should determine a weapon first, and get a valid ability?

				// not sure about this approach!
				28) magicitems & magicitemsweightedlookup tables adjusted
D					a) miwl records added
D						i) current 'type=9' records become category 1, weighted 1
D						ii) type=13 become category 2, weighted 1
D						iii) type=14 becomes category 3, weighted 1
X					b) wondrous items in mi reclassified as 'type' 9
X						i) remove 13, 14 from itemtypes table

?				29) limit # weapons/ armor etc?

		Unit Tests:
		1) rate of weighted options
D			a) item type (testing.cfm)
			b) enhancement bonus
D				1) armor
D				ii) shield
D				iii) weapons
			c) special abilities
D				1) armor
D				ii) shield
D				iii) weapons - melee
D				iv) weapons - ranged
			d) specific items
				1) armor
				ii) shield
				iii) weapons
				iv) rods
				v) staves
				vi) wondrous items
					a) minor
					b) medium
					c) major
			e) spells per scoll
			f) scroll spell levels
	--->


	<cffunction name="GetArmor" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfargument name="IsMagic" required="false" default="false" type="boolean">
<cftry>

		<cfif arguments.IsMagic>
			<cfquery name="local.GetBaseStats" datasource="#this.DSN#">
				select EnhancementBonus, Special, Price
				from magicarmorandshields
				where categoryid = #arguments.ItemCategory#
				order by -LOG(1 - rand()) / weighted asc
				limit 1
			</cfquery>

			<cfif local.GetBaseStats.Special is "Specific">
				<!--- get specific armor --->
				<cfset local.GetItem = GetMagicItem(arguments.ItemCategory, 15)>
				<!---<cfquery name="local.GetItem" datasource="#this.DSN#">
					select mi.name, mi.Price, mi.CL
					from magicitems mi
					join magicitemsweightedlookup miwl on mi.ID = miwl.ItemID
					where mi.itemtypeid = 15
						and mi.artifacttypeid is null
						and miwl.CategoryID = #arguments.ItemCategory#
					order by -(Log(1 - rand()) / miwl.weighted asc
					limit 1
				</cfquery>--->
			<cfelseif val(local.GetBaseStats.EnhancementBonus) gt 0>
				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', #local.GetBaseStats.EnhancementBonus#, ' ', a.name) as name,
						a.MasterworkPrice + #local.GetBaseStats.Price# as Price
					from armor a
					order by rand() desc
					limit 1
				</cfquery>
			<cfelse>

				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select a.name, Greatest(sa.CL, 3* maas.enhancementbonus) as CL, sa.name as ability, a.MasterworkPrice + eb.Price + sa.BasePriceModifier as Price, maas.enhancementbonus

					from armor a, magicarmorspecialabilitiesweightedlookup masawl
						inner join magicarmorandshields maas on maas.CategoryID = masawl.categoryid
						inner join specialabilities sa on sa.id = masawl.specialabilityid
						inner join enhancementbonus eb on eb.EnhancementBonus = maas.enhancementbonus + sa.EnhancementBonusModifier

					where masawl.categoryid = #arguments.ItemCategory# and eb.ItemType = 15
						and maas.EnhancementBonus > 0
					order by -LOG(1 - rand()) / masawl.weighted asc, -LOG(1 - rand()) / maas.Weighted asc
					limit 1
				</cfquery>
			</cfif>
		<cfelse>
			<cfquery name="local.GetItem" datasource="#this.DSN#">
				select concat("Masterwork ", a.Name) as Name, a.MasterworkPrice as Price
				from armor a
				order by rand() desc
				limit 1
			</cfquery>
		</cfif>
<cfcatch>
	<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>
</cftry>
		<cfreturn local.GetItem>

	</cffunction>

	<cffunction name="GetArt" access="remote" output="false" securejson="false" displayname="GetArt" returntype="struct" description="get gems">
		<cfargument name="AverageValue" required="true" type="numeric">

		<cfset local.Art = {}>

		<cfquery name="local.ArtClassification" datasource="#this.DSN#">
			select at.Arttypeid, at.numdice, at.dietype, at.modifier, at.modifieroperand, at.cointypeid, at.averagevalue, a.Description

			from Art a
			join Arttypelinks atl on atl.Artid = a.Artid
			join ArtTypes at on at.Arttypeid = atl.Arttypeid

			where at.averagevalue <= #Max(55, arguments.AverageValue)#
			group by a.Description, at.AverageValue
			order by at.averagevalue desc, rand()
			limit 1
		</cfquery>


		<cfset local.Art.Name = local.ArtClassification.Description>
		<cfset local.Art.Price = GetDieResults(local.ArtClassification.NumDice, local.ArtClassification.DieType, local.ArtClassification.Modifier, local.ArtClassification.ModifierOperand)>

		<cfreturn local.Art>
	</cffunction>

	<cffunction name="GetBaseTreasure" access="remote" output="false">
		<cfargument name="CR" required="true" type="numeric">
		<cfargument name="ProgressionID" required="true" type="numeric">
		<cfargument name="TreasureTypeID" required="true" type="numeric">

		<cfset local.IsError = false>
		<cfset local.ErrorMessage = []>

		<!--- get treasure info --->
		<cftry>
			<cfquery name="local.GetBaseTreasureInfo" datasource="#this.DSN#">
				select MaxGoldPieceValue * (select Multiplier from TreasureTypes where TreasureTypeID = #arguments.TreasureTypeID#) as MaxGPValue,
					ChanceMinorItems, ChanceMediumItems, ChanceMajorItems,
					MaxMediumItems, MaxMajorItems
				from BaseTreasure
				where CR = #arguments.CR# and ProgressionID = #arguments.ProgressionID#
			</cfquery>
			<cfcatch>
				<cfset local.IsError = true>
				<cfset ArrayAppend(local.ErrorMessage, "Call to GetBaseTressureInfo failed.")>
				<cfset local.cfcatch = duplicate(cfcatch)>
			</cfcatch>
		</cftry>

		<cfreturn local>
	</cffunction>

	<cffunction name="GetCoinSplit" access="remote" output="false" securejson="false" displayname="GetCoinSplit" returntype="struct" description="get gems">
		<cfargument name="Value" required="true" type="numeric">
		<cfargument name="CR" required="false" type="numeric" default="1">

		<cfset local.Coins = {
			Platinum = 0,
			Gold = 0,
			Silver = 0,
			Copper = 0
		}>
		<cfset local.Value = arguments.Value>

		<!--- copper split --->
		<cfif local.Value gt 0>
			<cfset local.PercentCopper = rand() * (-2 * arguments.CR + 43) / 100>
			<cfset local.Coins.Copper = Int(local.Value * ( local.PercentCopper) * 100)>
			<cfset local.Value -= local.Coins.Copper / 100>
		</cfif>

		<!--- silver split --->
		<cfif local.Value gt 0>
			<cfset local.PercentSilver = rand() * (-1.6 * arguments.CR + 41.5) / 100>
			<cfset local.Coins.Silver = Int(local.Value * ( local.PercentSilver) * 10)>
			<cfset local.Value -= local.Coins.Silver / 10>
		</cfif>

		<!--- Gold split --->
		<cfif local.Value gt 0>
			<cfset local.PercentGold = rand() * (1.6 * arguments.CR + 8) / 100>
			<cfset local.Coins.Gold = Int(local.Value * local.PercentGold)>
			<cfset local.Value -= local.Coins.Gold>
		</cfif>

		<cfif local.Value gt 0>
			<cfset local.Coins.Platinum = Int(local.Value / 10)>
			<cfset local.Value -= local.Coins.Platinum * 10>
		</cfif>

		<cfreturn local.Coins>
	</cffunction>

	<cffunction name="GetCoinsGemsArt" access="remote" output="false" displayname="GetCoinsGemsArt" returntype="any" description="get coins, gems and jewels, and art">
		<cfargument name="CR" required="true" type="numeric">
		<cfargument name="ProgressionID" required="true" type="numeric">
		<cfargument name="Value" required="true" type="numeric">

		<cfset local.Treasure = {Gems = {},
								Art = {},
								Coins = {},
								Value = 0
								}>

		<cfswitch expression="arguments.ProgressionID">
			<cfcase value="1">
				<cfset local.CRAdjustment = .5>
			</cfcase>
			<cfcase value="3">
				<cfset local.CRAdjustment = 1.5>
			</cfcase>
			<cfdefaultcase>
				<cfset local.CRAdjustment = 1>
			</cfdefaultcase>
		</cfswitch>

		<cfset local.WeightedCR = arguments.CR * local.CRAdjustment>
		<cfset local.GemItemValueMax = (10 + 2 * local.WeightedCR) * rand("SHA1PRNG") / 100 * arguments.Value>
		<cfset local.Treasure.Gems = GetGems(GemItemValueMax)>

		<cfset local.ArtItemValueMax = (10 + 1.5 * arguments.CR) * rand("SHA1PRNG") / 100 * arguments.Value>
		<cfset local.Treasure.Art = GetArt(ArtItemValueMax)>

		<cfset local.CoinValue = arguments.Value - local.Treasure.Gems.Price * local.Treasure.Gems.Quantity - local.Treasure.Art.Price>

		<cfif local.CoinValue gt 0>
			<cfset local.Treasure.Coins = GetCoinSplit(local.CoinValue, local.WeightedCR)>
		</cfif>

		<!--- todo: procedure for determining gems/art/coins
				3) randomly distribute remaining value over cointTypes, weighted towards larger coin types for higher CR and smaller coin types for lower CR
		--->

		<cfreturn local.Treasure>
	</cffunction>

	<cffunction name="GetDieResults" access="remote" output="false">
		<cfargument name="NumberOfDice" required="true" type="numeric">
		<cfargument name="DieType" required="true" type="numeric">
		<cfargument name="Modifier" required="false" default="0" type="numeric">
		<cfargument name="ModifierOpperand" required="false" default="+" type="string">

		<cfset local.ErrorMessage = []>
		<cfset local.IsError = false>

		<cfset local.Result = 0>

		<cfloop from="1" to="#arguments.NumberOfDice#" index="local.thisRoll">
			<cfset local.Result += RandRange(1, arguments.DieType, "SHA1PRNG")>
		</cfloop>

		<cfset local.Result = evaluate(local.Result & arguments.ModifierOpperand & arguments.Modifier)>

		<cfreturn local.Result>
	</cffunction>

	<cffunction name="GetGems" access="remote" output="false" securejson="false" displayname="GetGems" returntype="struct" description="get gems">
		<cfargument name="AverageValue" required="true" type="numeric">

		<cfset local.Gems = {}>

		<cfquery name="local.GemClassification" datasource="#this.DSN#">
			select gt.gemtypeid, gt.numdice, gt.dietype, gt.modifier, gt.modifieroperand, gt.cointypeid, gt.averagevalue, g.name

			from gemtypes gt
			join gemtypelinks gtl on gtl.gemtypeid = gt.gemtypeid
			join gems g on g.gemid = gtl.gemid

			where gt.averagevalue <= #Max(10, arguments.AverageValue)#
			order by rand()
			limit 1
		</cfquery>

		<cfset local.Gems.Name = local.GemClassification.Name>
		<cfset local.Gems.Price = GetDieResults(local.GemClassification.NumDice, local.GemClassification.DieType, local.GemClassification.Modifier, local.GemClassification.ModifierOperand)>
		<cfset local.Gems.Quantity = Max(1, IIf(GetDieResults(1,100) lt 50, int(arguments.AverageValue / local.Gems.Price), ceiling(arguments.AverageValue / local.Gems.Price)))>

		<cfreturn local.Gems>
	</cffunction>

	<cffunction name="GetGoods" access="remote" output="false">
		<cfargument name="MinValue" required="false" default="0" type="numeric">
		<cfargument name="MaxValue" required="false" default="0" type="numeric">
<cftry>

		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select g.Name, g.Price * c.Multiplier as Price
			from Goods g
			join cointypes c on c.ID = g.CoinTypeID
			<cfif arguments.MinValue gt 0 or arguments.MaxValue gt 0>
			where
				<cfif arguments.MinValue gt 0>Price >= #arguments.MinValue#</cfif>
				<cfif arguments.MinValue gt 0 and arguments.MaxValue gt 0>
					and
				</cfif>
				<cfif arguments.MaxValue gt 0>Price <= #arguments.MaxValue#</cfif>
			</cfif>
			order by rand() desc
			limit 1
		</cfquery>
<cfcatch>
<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>
</cftry>
		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetItem" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->

		<cfset local.GetItem = StructNew()>

		<!--- determine item type, and gp range, if any --->
<cftry>
		<cfquery name="local.GetItemType" datasource="#this.DSN#">
			SELECT ic.ID, ic.Weighted, ic.ItemTypeID, ic.MaxValue, ic.MinValue, it.Type
			FROM itemcategories ic
			join ItemTypes it on it.ID = ic.ItemTypeID
			where ic.id = #arguments.ItemCategory#
			ORDER BY -LOG(1 - rand()) / ic.weighted asc
			LIMIT 1
		</cfquery>
		<cfset local.GetItem.ItemType = local.GetItemType.Type>
<cfcatch>
<cfset local.GetItem.Error = duplicate(cfcatch)>
</cfcatch>
</cftry>
		<cfif IsQuery(local.GetItemType)>
<cftry>
			<cfswitch expression="#local.GetItemType.ItemTypeID#">
				<cfcase value="1">
					<cfset local.GetItem.Item = GetArmor(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="2">
					<cfset local.GetItem.Item = GetWeapon(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="3">
					<cfset local.GetItem.Item = GetShield(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="4">
					<cfset local.GetItem.Item = GetGoods()><!---Val(local.GetItemType.MinValue), Val(local.GetItemType.MaxValue)--->
				</cfcase>
				<cfcase value="5">
					<cfset local.GetItem.Item = GetTradeGoods()><!---Val(local.GetItemType.MinValue), Val(local.GetItemType.MaxValue)--->
				</cfcase>
				<cfcase value="6">
					<cfset local.GetItem.Item = GetPotion()>
				</cfcase>
				<cfcase value="7">
					<cfset local.GetItem.Item = GetScroll(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="8, 9, 11, 12, 13, 14">
					<cfset local.GetItem.Item = GetMagicItem(arguments.ItemCategory, local.GetItemType.ItemTypeID)>
				</cfcase>
				<cfcase value="10">
					<cfset local.GetItem.Item = GetWand()>
				</cfcase>
				<cfcase value="15">
					<cfset local.GetItem.Item = GetMagicArmor(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="16">
					<cfset local.GetItem.Item = GetMagicShield(arguments.ItemCategory)>
				</cfcase>
				<cfcase value="17">
					<cfset local.GetItem.Item = GetMagicWeapon(arguments.ItemCategory)>
				</cfcase>
				<!---<cfdefaultcase>
					<cfset local.GetItem.Item = QueryNew("empty")>
					<cfset QueryAddRow(local.GetItem)>
				</cfdefaultcase>--->
			</cfswitch>

			<!--- convert GetItem.Item query into a struct --->
			<cfloop query="local.GetItem.Item">
				<cfset local.tempStruct = {}>
				<cfloop list="#local.GetItem.Item.ColumnList#" index="local.thisItem">
					<cfset local.tempStruct[local.thisItem] = local.GetItem.Item[local.thisitem][local.GetItem.Item.CurrentRow]>
				</cfloop>
			</cfloop>

			<cfset local.GetItem.Item = local.tempStruct>

			<cfset local.GetItem.Item.ItemCategory = arguments.ItemCategory>

			<cfif StructKeyExists(local.GetItem.Item, 'MaxQuantity')>
				<cfif local.GetItem.Item.MaxQuantity gt 1>
					<!--- calculate proportion of charges and price --->
					<cfset local.Proportion = GetDieResults(1, local.GetItem.Item.MaxQuantity) / local.GetItem.Item.MaxQuantity>
					<cfset local.GetItem.Item.Quantity = Int(local.GetItem.Item.MaxQuantity * local.Proportion)>
					<cfset local.GetItem.Item.Price = local.GetItem.Item.Price * local.Proportion>
				</cfif>
				<cfset StructDelete(local.GetItem.Item, 'MaxQuantity')>
			</cfif>
<cfcatch>
<cfset local.GetItem.Error = duplicate(cfcatch)>
</cfcatch>
</cftry>
		</cfif>

		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetMagicArmor" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->

		<cfreturn GetArmor(arguments.ItemCategory, 1 )>
	</cffunction>

	<cffunction name="GetMagicItem" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfargument name="ItemTypeID" required="true" type="numeric">

		<cfif arguments.ItemTypeID eq 9>
			<cfset local.ItemCategory = 1>
		<cfelseif arguments.ItemTypeID eq 13>
			<cfset local.ItemCategory = 2>
		<cfelseif arguments.ItemTypeID eq 14>
			<cfset local.ItemCategory = 3>
		<cfelse>
			<cfset local.ItemCategory = arguments.ItemCategory>
		</cfif>
<cftry>

		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select mi.Name, mi.Price, mi.Description, mi.CL
			from MagicItems mi
				join magicitemsweightedlookup miwl on mi.id = miwl.ItemID
			where mi.ItemTypeID = #arguments.ItemTypeID#
				and mi.artifacttypeid is null
				and miwl.CategoryID = #local.ItemCategory#
			order by -LOG(1 - rand()) / miwl.weighted asc
			limit 1
		</cfquery>
<cfcatch>
<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>
</cftry>
		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetMagicShield" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfreturn GetShield(arguments.ItemCategory, 1)>
	</cffunction>

	<cffunction name="GetMagicWeapon" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfreturn GetWeapon(arguments.ItemCategory, 1 )>
	</cffunction>

	<cffunction name="GetMajorItem" access="remote" output="false">
		<cfreturn GetItem(3)>
	</cffunction>

	<cffunction name="GetMediumItem" access="remote" output="false">
		<cfreturn GetItem(2)>
	</cffunction>

	<cffunction name="GetMinorItem" access="remote" output="false">
		<cfreturn GetItem(1)>
	</cffunction>

	<cffunction name="GetPotion" access="remote" output="false">
		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select distinct s.name, stl.minspelllevel as SpellLevel, stl.mincasterlevel as CL, Greatest(50 * stl.minspelllevel * stl.mincasterlevel, 25) + s.componentcost as Price
			from spells s
			join spelltypelinks stl on stl.spellid = s.id
			where s.potionsallowed = 1
			order by rand() desc
			limit 1
		</cfquery>

		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetScroll" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
<cftry>

		<cfquery name="local.SpellsPerScroll" datasource="#this.DSN#">
			select spellsperscroll
			from spellsperscrolllookup
			order by -LOG(1 - rand()) / weighted asc
			limit 1
		</cfquery>

		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select scsl.SpellLevel, stl.mincasterlevel as CL, stl.spellcastertypeid, concat(sct.name, ' ', 'Scroll') as name,
				Greatest(stl.MinSpellLevel * stl.MinCasterLevel * 25, 12.5) * #local.SpellsPerScroll.SpellsPerScroll# as Price
			from ScrollSpellLevels scsl
			join spelltypelinks stl
			inner join spellcastertypes sct on sct.id = stl.spellcastertypeid
			where stl.spellcastertypeid = (select id from spellcastertypes order by -LOG(1 - rand()) / scrollweight asc limit 1)
				and stl.minspelllevel = scsl.SpellLevel
			order by -LOG(1 - rand()) / scsl.weight asc
			limit 1
		</cfquery>

		<cfquery name="local.GetSpells" datasource="#this.DSN#">
			select s.name, #local.GetItem.Price# + s.componentcost as Price
			from spells s
			inner join spelltypelinks stl on stl.spellid = s.id
			where stl.spellcastertypeid = #local.GetItem.Spellcastertypeid#
			and stl.minspelllevel = #local.GetItem.SpellLevel#
			order by rand() desc
			limit #local.SpellsPerScroll.SpellsPerScroll#
		</cfquery>

		<cfset QueryAddColumn(local.GetItem, "SpellName", "varchar", [])>
		<cfset QuerySetCell(local.GetITem, "SpellName", #ValueList(local.GetSpells.Name,'|')#)>
		<cfset QuerySetCell(local.GetITem, "Price", #local.GetSpells.Price#)>
<cfcatch>
	<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>

</cftry>
		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetShield" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfargument name="IsMagic" required="false" default="false" type="boolean">

		<cfif arguments.IsMagic>
			<cfquery name="local.GetBaseStats" datasource="#this.DSN#">
				select EnhancementBonus, Price, Special
				from magicarmorandshields
				where categoryid = #arguments.ItemCategory#
				order by -LOG(1 - rand()) / weighted asc
				limit 1
			</cfquery>

			<cfif local.GetBaseStats.Special is "Specific">
				<!--- todo: weight specific items? --->

				<!--- get specific shield --->
				<cfset local.GetItem = GetMagicItem(arguments.ItemCategory, 16)>
				<!---<cfquery name="local.GetItem" datasource="#this.DSN#">
					select mi.name, mi.Price, mi.CL
					from magicitems mi
						join magicitemsweightedlookup miwl on mi.ID = miwl.ItemID
					where mi.itemtypeid = 16
						and mi.artifacttypeid is null
						and miwl.CategoryID = #arguments.ItemCategory#
					order by rand() desc
					limit 1
				</cfquery>--->
			<cfelseif val(local.GetBaseStats.EnhancementBonus) gt 0>
				<!--- get item to match enhancement bonus returned --->
				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', #local.GetBaseStats.EnhancementBonus#, ' ', sh.name) as name, sh.MasterworkPrice + #local.GetBaseStats.Price# as Price
					from shields sh
					order by rand() desc
					limit 1
				</cfquery>
			<cfelse>
				<!--- todo: rare case: 2 abilities --->
				<!--- get item, enhancement bonus and special ability --->

				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', maas.EnhancementBonus, ' ', sh.name) as name, sa.CL, sa.name as ability,
						sh.MasterworkPrice + eb.Price + sa.BasePriceModifier as Price
					from shields sh
					inner join magicshieldspecialabilitieslinks mssal on mssal.shieldid = sh.id
					inner join magicshieldspecialabilitiesweightedlookup mssawl on mssawl.specialabilityid = mssal.specialabilityid
					inner join specialabilities sa on sa.id = mssal.specialabilityid
					inner join magicarmorandshields maas on maas.categoryid
					inner join enhancementbonus eb on eb.EnhancementBonus = maas.EnhancementBonus + sa.EnhancementBonusModifier
					where mssawl.categoryid = #arguments.ItemCategory# and eb.ItemType = 16
						and maas.enhancementbonus > 0
					order by -LOG(1 - rand()) / mssawl.weighted asc, -LOG(1 - rand()) / maas.Weighted asc
					limit 1
				</cfquery>
			</cfif>

		<cfelse>
			<cfquery name="local.GetItem" datasource="#this.DSN#">
				select concat("Masterwork ", s.Name) as Name, s.MasterworkPrice as Price
				from shields s
				order by rand() desc
				limit 1
			</cfquery>
		</cfif>

		<cfreturn local.GetItem>

	</cffunction>

	<cffunction name="GetTradeGoods" access="remote" output="false">
		<cfargument name="MinValue" required="false" default="0" type="numeric">
		<cfargument name="MaxValue" required="false" default="0" type="numeric">
<cftry>

		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select tg.Name, tg.Price * c.Multiplier as Price, tg.Quantity
			from TradeGoods tg
			join cointypes c on c.ID = tg.CoinTypeID
			<cfif arguments.MinValue gt 0 or arguments.MaxValue gt 0>
			where
				<cfif arguments.MinValue gt 0>Price >= #arguments.MinValue#</cfif>
				<cfif arguments.MinValue gt 0 and arguments.MaxValue gt 0>
					and
				</cfif>
				<cfif arguments.MaxValue gt 0>Price <= #arguments.MaxValue#</cfif>
			</cfif>
			order by rand() desc
			limit 1
		</cfquery>
<cfcatch>
<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>
</cftry>
		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetTreasure" access="remote" output="false">
		<cfargument name="CR" required="true" type="numeric">
		<cfargument name="ProgressionID" required="true" type="numeric">
		<cfargument name="TreasureTypeID" required="true" type="numeric">
		<cfargument name="CGA" required="false" type="numeric" default="0">

		<cfset local.IsError = false>
		<cfset local.ErrorMessage = []>
		<cfset local.Treasure = {
				'TotalGPV' = 0,
				'Coins' = [],
				'Gems/Jewels/Art' = [],
				'Armor' = [],
				'Weapons' = [],
				'Shields' = [],
				'Goods' = [],
				'Trade Goods' = [],
				'Potions' = [],
				'Scrolls' = [],
				'Rings' = [],
				'Minor Wondrous Items' = [],
				'Wands' = [],
				'Staves' = [],
				'Rods' = [],
				'Medium Wondrous Items' = [],
				'Major Wondrous Items' = [],
				'Magic Armor' = [],
				'Magic Shields' = [],
				'Magic Weapons' = []
			}>

		<!--- todo: generate treasure details
			x	1) get base info (max GPV, chance and max med/major items)
			x	2) while local.GPTotal < max GPV
				x	a) check for major items, if any chances remain
				x	b) check for medium items, if any chances remain
				3) determine coins/gems percent and breakdown
		 --->

		<cfset local.BaseTreasureInfo = GetBaseTreasure(arguments.CR, arguments.ProgressionID, arguments.TreasureTypeID)>

		<cfif not local.BaseTreasureInfo.IsError>
			<cfset local.Treasure['MaxGPV'] = local.BaseTreasureInfo.GetBaseTreasureInfo.MaxGPValue>
			<cfset local.ChanceMinorItems = local.BaseTreasureInfo.GetBaseTreasureInfo.ChanceMinorItems>
			<cfset local.ChanceMediumItems = local.BaseTreasureInfo.GetBaseTreasureInfo.ChanceMediumItems>
			<cfset local.ChanceMajorItems = local.BaseTreasureInfo.GetBaseTreasureInfo.ChanceMajorItems>
			<cfset local.MaxMediumItems = local.BaseTreasureInfo.GetBaseTreasureInfo.MaxMediumItems>
			<cfset local.MaxMajorItems = local.BaseTreasureInfo.GetBaseTreasureInfo.MaxMajorItems>
		<cfelse>
			<cfset local.IsError = true>
			<cfset ArrayAppend(local.ErrorMessage, local.BaseTreasureInfo.ErrorMessage)>
		</cfif>
<cftry>

		<cfif arguments.CGA gt 0>
			<cfset local.ThisItem = GetCoinsGemsArt(arguments.CR, arguments.ProgressionID, local.Treasure['MaxGPV'] * arguments.CGA / 100)>
			<cfif not StructIsEmpty(local.ThisItem.Gems)>
				<cfset ArrayAppend(local.Treasure['Gems/Jewels/Art'], local.ThisItem.Gems)>
				<cfset ArrayAppend(local.Treasure['Gems/Jewels/Art'], local.ThisItem.Art)>
				<cfset local.Treasure.TotalGPV += local.ThisItem.Gems.Price * local.ThisItem.Gems.Quantity + local.ThisItem.Art.Price>
			</cfif>
			<cfif not StructIsEmpty(local.ThisItem.Coins)>
				<cfset ArrayAppend(local.Treasure['Coins'], local.ThisItem.Coins)>
				<cfset local.Treasure.TotalGPV += local.ThisItem.Coins.Copper / 100>
				<cfset local.Treasure.TotalGPV += local.ThisItem.Coins.Silver / 10>
				<cfset local.Treasure.TotalGPV += local.ThisItem.Coins.Gold>
				<cfset local.Treasure.TotalGPV += local.ThisItem.Coins.Platinum * 10>
			</cfif>
		</cfif>

		<!---<cfset local.Treasure.BaseTreasureInfo = local.BaseTreasureInfo>--->
		<cfset local.Attempts = 0>
		<cfset local.AttemptsMajorItems = 0>
		<cfset local.AttemptsMediumItems = 0>

		<cfloop condition="#local.Attempts# lt 100 and #local.Treasure.TotalGPV# lt #local.Treasure.MaxGPV# and not #local.IsError#">
			<cfset local.ThisItem = {Item = ""}>
			<cfif local.MaxMajorItems gt 0 and local.AttemptsMajorItems lt local.MaxMajorItems>
				<cfif GetDieResults(1, 100) lte local.ChanceMajorItems>
					<cfset local.ThisItem = GetMajorItem()>
				</cfif>
				<cfset local.AttemptsMajorItems ++>
			</cfif>
			<cfif not IsStruct(local.ThisItem.Item) and local.MaxMediumItems gt 0 and local.AttemptsMediumItems lt local.MaxMediumItems>
				<cfif GetDieResults(1, 100) lte local.ChanceMediumItems>
					<cfset local.ThisItem = GetMediumItem()>
				</cfif>
				<cfset local.AttemptsMediumItems ++>
			</cfif>
			<cfif not IsStruct(local.ThisItem.Item)>
				<cfset local.ThisItem = GetMinorItem()>
			</cfif>
			<cfif IsStruct(local.ThisItem.Item) and not StructIsEmpty(local.ThisItem.Item)>
				<cfif (local.ThisItem.Item.Price + local.Treasure.TotalGPV) lte local.Treasure.MaxGPV or GetDieResults(1, 100) gte 50 or local.Treasure.TotalGPV eq 0>
					<!---<cfset ArrayAppend(local.Treasure, local.ThisItem)>--->
					<cftry>
						<cfset ArrayAppend(local.Treasure[local.ThisItem.ItemType], local.ThisItem.Item)>

						<cfset local.Treasure.TotalGPV += local.ThisItem.Item.Price>
						<cfcatch>
							<cfset local.Treasure.Error = duplicate(cfcatch)>
							<cfset local.Attempts += 100>
						</cfcatch>
					</cftry>
				<cfelse>
					<cfset local.Treasure.RejectedItem = [local.ThisItem.Item]>
					<cfset local.Attempts += 100>
				</cfif>
			</cfif>
			<cfset local.Attempts++>
		</cfloop>
		<!---<cfset local.ThisItem = GetMajorItem()>
		<cfset ArrayAppend(local.Treasure[local.ThisItem.ItemType], local.ThisItem.Item)>--->
<cfcatch>
	<cfset local.Treasure = duplicate(cfcatch)>
</cfcatch>
</cftry>

		<cfreturn local.Treasure>
	</cffunction>

	<cffunction name="GetWand" access="remote" output="false">
		<cfquery name="local.GetItem" datasource="#this.DSN#">
			select distinct s.name, stl.minspelllevel as SpellLevel, stl.mincasterlevel as CL,
				Greatest(750 * stl.minspelllevel * stl.mincasterlevel, 375) + s.componentcost as Price
			from spells s
			join spelltypelinks stl on stl.spellid = s.id
			where s.wandsallowed = 1
			order by rand() desc
			limit 1
		</cfquery>

		<!--- todo: wands have a 30% chance of having an inscription/design etc indicating function/usage/command word
					need a lookup table of designs/inscriptions/command words etc
		--->

		<!--- calculate proportion of charges and price --->
		<cfset local.Proportion = GetDieResults(1, 100) / 100>
		<cfset local.Charges = Int(50 * local.Proportion)>
		<cfset local.Price = local.GetItem.Price * local.Charges / 50>

		<cfset QueryAddColumn(local.GetItem, "Charges", "decimal", [])>
		<cfset QuerySetCell(local.GetItem, "Charges", local.Charges)>
		<cfset QuerySetCell(local.GetItem, "Price", local.Price)>

		<cfreturn local.GetItem>
	</cffunction>

	<cffunction name="GetWeapon" access="remote" output="false">
		<cfargument name="ItemCategory" required="true" type="numeric"><!--- 1 = minor, 2 = medium, 3 = major --->
		<cfargument name="IsMagic" required="false" default="false" type="boolean">
<!--- todo:
			4) check for magic (or already checked as passed in)
				a) not magic, return masterwork weapon (possibly of appropriate special material)
				b) magic:
					i) check for enhancement bonus
						1) numeric enhancement bonus = return +[EB] weapon
						2) "specific" = return specific magic weapon from magic items table (this overrules the weapon type generated)
						3) "special ability" = get valid spec ability for weapon, and get a valid enhancement bonus; return +[EB] weapon of [Special Ability]
							a) rare occurrance of 2 special abilities
					ii) check for "glowing" 30% chance
--->
		<!--- get base weapon, weighted by weapon category --->
<!--- doesn't account for multiple damage types per weapon
select w.Name, w.ID, wtl.typeid, dtl.damagetypeid
from weapons w
join weapontypelinks wtl on w.id = wtl.weaponid
join damagetypelinks dtl on dtl.weaponid = w.id
where wtl.categoryid = (select id from weaponcategories order by -LOG(1 - rand()) / weighted asc limit 1)
order by rand() desc
limit 1
--->
<cftry>

		<cfquery name="local.GetBaseItem" datasource="#this.DSN#"><!--- doesn't account for multiple damage types per weapon --->
			select w.Name, w.ID, w.MaxQuantity, w.MasterworkPrice as Price, wtl.typeid as WeaponTypeID,
			(select group_concat(damagetypeid) as DamageTypeID from damagetypelinks where damagetypelinks.weaponid = w.id) as DamageTypeIDs

			from weapons w
			join weapontypelinks wtl on w.id = wtl.weaponid

			where wtl.categoryid = (select id from weaponcategories order by -LOG(1 - rand()) / weighted asc limit 1)
			order by rand() desc
			limit 1
		</cfquery>
		<!--- todo: 10% chance of being small sized --->
		<!--- todo: check for special materials --->

		<cfif arguments.IsMagic>
			<cfquery name="local.GetBaseStats" datasource="#this.DSN#">
				select mw.EnhancementBonus, mw.Special
				from magicweapons mw
				where mw.categoryid = #arguments.ItemCategory#
				order by -LOG(1 - rand()) / mw.weighted asc
				limit 1
			</cfquery>

			<cfif local.GetBaseStats.Special is "Specific">
				<!--- todo: add weighting to specific items --->

				<!--- get specific weapon --->
				<cfset local.GetItem = GetMagicItem(arguments.ItemCategory, 17)>
				<!---<cfquery name="local.GetItem" datasource="#this.DSN#">
					select name, Price, CL
					from magicitems
					where itemtypeid = 17
						and artifacttypeid is null
					order by rand() desc
					limit 1
				</cfquery>--->
			<cfelseif val(local.GetBaseStats.EnhancementBonus) gt 0>
				<!--- get enhancement bonus --->
				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', mw.EnhancementBonus, ' ', '#local.GetBaseItem.Name#') as Name, #local.GetBaseItem.Price# + eb.Price as Price, #local.GetBaseItem.MaxQuantity# as MaxQuantity
					from magicweapons mw
					join enhancementbonus eb on mw.enhancementbonus = eb.enhancementbonus
					where mw.categoryid = #arguments.ItemCategory#
					and mw.EnhancementBonus is not null
					order by -LOG(1 - rand()) / mw.weighted asc
					limit 1
				</cfquery>
					<!--- get item to match enhancement bonus returned --->
				<!---<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', #local.GetBaseStats.EnhancementBonus#, ' ', w.name) as name, w.MasterworkPrice + eb.Price as Price
					from weapons w
					join enhancementbonus eb
					join weapontypelinks wtl on w.id = wtl.weaponid
					where eb.EnhancementBonus = #local.GetBaseStats.EnhancementBonus# and eb.ItemType = 17
						and wtl.CategoryID = (select id from weaponcategories order by -LOG(1 - rand()) / weighted asc limit 1)
					order by rand() desc
					limit 1
				</cfquery>--->
			<cfelse>
<!---
				3) "special ability" = get valid spec ability for weapon, and get a valid enhancement bonus; return +[EB] weapon of [Special Ability]
					a) rare occurrance of 2 special abilities
			ii) check for "glowing" 30% chance
--->
				<!--- get base enhancement bonus --->
				<cfquery name="local.GetBaseStats" datasource="#this.DSN#">
					select mw.EnhancementBonus
					from magicweapons mw
					where mw.categoryid = #arguments.ItemCategory#
					and mw.EnhancementBonus is not null
					order by -LOG(1 - rand()) / mw.weighted asc
					limit 1
				</cfquery>
				<!--- get valid special ability --->
				<!--- todo: rare case: 2 abilities
						Treasure Category: 1 = 1%
						Treasure Category: 2 = 4%
						Treasure Category: 3 = 10%
				--->
				<cfquery name="local.GetItem" datasource="#this.DSN#">
<!---select sa.CL, sa.name as ability, sa.BasePriceModifier + eb.Price as Price, mw.EnhancementBonus

from magicweaponspecialabilitiesweightedlookup mwsawl
	join magicweapons mw on mw.categoryid = mwsawl.categoryid
	join magicweaponspecialabilitieslinks mwsal on mwsawl.specialabilityid = mwsal.specialabilityid
	join specialabilities sa on sa.id = mwsawl.specialabilityid
	join enhancementbonus eb

where mwsawl.CategoryID = 1 and mwsawl.weapontypeid = 1 and eb.itemtype = 17
	and (mwsal.DamageTypeID in (1,2,3) or mwsal.DamageTypeID is null)
	and eb.enhancementbonus = mw.EnhancementBonus + sa.EnhancementBonusModifier

order by -LOG(1 - rand()) / mwsawl.weighted asc, -LOG(1 - rand()) / mw.weighted asc
limit 1--->

					select concat('+', mw.EnhancementBonus, ' ', '#local.GetBaseItem.name#') as name, Greatest(sa.CL, 3*eb.enhancementbonus) as CL,
						sa.name as ability, sa.BasePriceModifier + eb.Price + #local.GetBaseItem.Price# as Price
					from magicweaponspecialabilitiesweightedlookup mwsawl
						join magicweapons mw on mw.categoryid = mwsawl.categoryid
						left join magicweaponspecialabilitieslinks mwsal on mwsawl.specialabilityid = mwsal.specialabilityid
						join specialabilities sa on sa.id = mwsawl.specialabilityid
						join enhancementbonus eb

					where mwsawl.CategoryID = #arguments.ItemCategory# and mwsawl.weapontypeid = #local.GetBaseItem.WeaponTypeID# and eb.itemtype = 17
						and (<cfif local.GetBaseItem.DamageTypeIDs neq "">mwsal.DamageTypeID in (#local.GetBaseItem.DamageTypeIDs#) or </cfif>mwsal.DamageTypeID is null)
						and eb.enhancementbonus = mw.EnhancementBonus + sa.EnhancementBonusModifier

					order by -LOG(1 - rand()) / mwsawl.weighted asc, -LOG(1 - rand()) / mw.weighted asc
					limit 1
				</cfquery>


				<!---<cfquery name="local.GetBaseAbility" datasource="#this.DSN#">
					select sa.CL, sa.name as ability, sa.BasePriceModifier, sa.EnhancementBonusModifier, mwsal.WeaponTypeID, mwsal.DamageTypeID
					from specialabilities sa
					inner join magicweaponspecialabilitiesweightedlookup mwsawl on mwsawl.specialabilityid = sa.id
					inner join magicweaponspecialabilitieslinks mwsal on sa.id = mwsal.specialabilityid
					where mwsawl.CategoryID = #arguments.ItemCategory#
					order by -LOG(1 - rand()) / mwsawl.weighted asc
					limit 1
				</cfquery>

				<!--- get valid weapon, with enhancement bonus and price --->
				<cfquery name="local.GetItem" datasource="#this.DSN#">
					select concat('+', #local.GetBaseStats.EnhancementBonus#, ' ', w.name) as name, '#local.GetBaseAbility.Ability#' as ability, '#local.GetBaseAbility.CL#' as CL,
						w.MasterworkPrice + eb.Price + #local.GetBaseAbility.BasePriceModifier# as Price
					from weapons w
					join enhancementbonus eb
					inner join weapontypelinks wtl on wtl.weaponid = w.id
					<!--- if restrictions, add join clauses --->
					<cfif local.GetBaseAbility.DamageTypeID is not "">
						inner join damagetypelinks dtl on dtl.damagetypeid = #local.GetBaseAbility.DamageTypeID#
					</cfif>
					where eb.enhancementbonus =  #local.GetBaseStats.EnhancementBonus# + #local.GetBaseAbility.EnhancementBonusModifier# and eb.ItemType = 17
					<cfif local.GetBaseAbility.WeaponTypeID is not "">
						and wtl.typeid = #local.GetBaseAbility.WeaponTypeID#
					</cfif>
						and wtl.CategoryID = (select id from weaponcategories order by -LOG(1 - rand()) / weighted asc limit 1)
					order by rand() desc
					limit 1
				</cfquery>--->

			</cfif>

		<cfelse>
			<cfquery name="local.GetItem" datasource="#this.DSN#">
				select concat("Masterwork ", w.Name) as Name, w.MaxQuantity, w.MasterworkPrice as Price
				from weapons w
				where w.id = #local.GetBaseItem.ID#
			</cfquery>
		</cfif>
<cfcatch>
	<cfset local.GetItem = duplicate(cfcatch)>
</cfcatch>

</cftry>
		<cfreturn local.GetItem>
	</cffunction>
</cfcomponent>